package com.epam.newsmanagement.database.dao.jdbc;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.epam.newsmanagement.database.dao.INewsDAO;
import com.epam.newsmanagement.database.dao.exception.DAOException;
import com.epam.newsmanagement.database.dbcp.ConnectionPool;
import com.epam.newsmanagement.database.dbcp.exception.PoolException;
import com.epam.newsmanagement.model.News;


public final class JDBCNewsDAO implements INewsDAO{
	private final static Logger logger = Logger.getLogger(JDBCNewsDAO.class);
	private final static String SQL_CREATE_NEWS = "INSERT INTO news(addDate, title, brief, content)" +
												"VALUES(?,?,?,?)";
	private final static String SQL_FETCH_NEWS_BY_ID = "SELECT * FROM news WHERE id = ";
	private final static String SQL_REMOVE_NEWS = "DELETE FROM news WHERE id IN (";
	private final static String SQL_READ_ALL_NEWS = "SELECT * FROM news ORDER BY addDate DESC";
	private final static String SQL_UPDATE_NEWS = "UPDATE news SET addDate = ?, title = ?, brief = ?, content = ? WHERE id = ?";
	private final static String SQL_GET_ID = "SELECT NEWS_SEQ.CURRVAL FROM DUAL";
	private ConnectionPool pool;

	@Override
	public void save(News news) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;
		Statement idStatement = null;
		Integer id = news.getId();
		try {
			connection = pool.getConnection();
			connection.setAutoCommit(false);
			if(id != null) {
				preparedStatement = connection.prepareStatement(SQL_UPDATE_NEWS);
				preparedStatement.setInt(5, id);
			} else{
				preparedStatement = connection.prepareStatement(SQL_CREATE_NEWS, Statement.RETURN_GENERATED_KEYS);
			}
			Date date = new Date(news.getDate().getTime());
			String title = news.getTitle();
			String brief = news.getBrief();
			String content = news.getContent();
			preparedStatement.setDate(1, date);
			preparedStatement.setString(2, title);
			preparedStatement.setString(3, brief);
			preparedStatement.setString(4, content);
			preparedStatement.executeUpdate();
			if(id == null){
				idStatement = connection.createStatement();
				generatedKeys = idStatement.executeQuery(SQL_GET_ID);
				if(generatedKeys.next()){
					news.setId(generatedKeys.getInt(1));
				}
			}
			connection.commit();
			connection.setAutoCommit(true);
		} catch (SQLException e){
			try {
				connection.rollback();
			} catch (SQLException e1) {
				logger.error(e1);
			}
			throw new DAOException(e);
		} catch(PoolException e){
			throw new DAOException(e);
		}finally {
			if(generatedKeys != null){
				try{
					generatedKeys.close();
				} catch(SQLException e) {
					logger.error(e);
				}
			}
			if(preparedStatement != null){
				try{
					preparedStatement.close();					
				}catch(SQLException e){
					logger.error(e);
				}
			}
			if(connection != null){
				try{
					connection.close();
				}catch(SQLException e){
					logger.error(e);
				}
			}
		}		
	}

	@Override
	public List<News> getList() throws DAOException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		List<News> newsList = new ArrayList<News>();
		try{
			connection = pool.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(SQL_READ_ALL_NEWS);
			while(resultSet.next()){
				int id = resultSet.getInt(1);
				Date date = resultSet.getDate(2);
				String title = resultSet.getString(3);
				String brief = resultSet.getString(4);
				String content = resultSet.getString(5);
				News news = new News(id, date, title, brief, content);
				newsList.add(news);
			}
		} catch(SQLException e){
			throw new DAOException(e);
		} catch(PoolException e){
			throw new DAOException(e);
		} finally {
			if(resultSet != null){
				try{
					resultSet.close();
				} catch(SQLException e) {
					logger.error(e);
				}
			}
			if(statement != null){
				try{
					statement.close();					
				}catch(SQLException e){
					logger.error(e);
				}
			}
			if(connection != null){
				try{
					connection.close();
				}catch(SQLException e){
					logger.error(e);
				}
			}
		}
		return newsList;
	}

	@Override
	public void remove(Integer[] selectedNews) throws DAOException {		
		if(selectedNews.length > 0){
			Connection connection = null;
			Statement statement = null;
			try{
				connection = pool.getConnection();
				statement = connection.createStatement();
				StringBuffer query = new StringBuffer(SQL_REMOVE_NEWS);
				for(int i : selectedNews){
						query.append(i);
						query.append(',');
				}
				int lastIndex = query.length() - 1;
				query.deleteCharAt(lastIndex);
				query.append(')');
				statement.executeUpdate(query.toString());
			} catch (SQLException e){
				throw new DAOException(e);
			} catch(PoolException e){
				throw new DAOException(e);
			}finally {
				if(statement != null){
					try{
						statement.close();					
					}catch(SQLException e){
						logger.error(e);
					}
				}
				if(connection != null){
					try{
						connection.close();
					}catch(SQLException e){
						logger.error(e);
					}
				}
			}
		}
		
	}

	@Override
	public News fetchById(int id) throws DAOException {
		News news = null;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try{
			connection = pool.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(SQL_FETCH_NEWS_BY_ID + id);
			while(resultSet.next()){
				Date date = resultSet.getDate(2);
				String title = resultSet.getString(3);
				String brief = resultSet.getString(4);
				String content = resultSet.getString(5);
				news = new News(id, date, title, brief, content);
			}			
		} catch (SQLException e) {
			throw new DAOException(e);
		} catch(PoolException e){
			throw new DAOException(e);
		} finally {
			if(resultSet != null){
				try{
					resultSet.close();
				} catch(SQLException e) {
					logger.error(e);
				}
			}
			if(statement != null){
				try{
					statement.close();					
				}catch(SQLException e){
					logger.error(e);
				}
			}
			if(connection != null){
				try{
					connection.close();
				}catch(SQLException e){
					logger.error(e);
				}
			}
		}
		return news;
	}
	public ConnectionPool getPool() {
		return pool;
	}

	public void setPool(ConnectionPool pool) {
		this.pool = pool;
	}
}
